<?php
namespace Home\Service;

use Home\Service\POBillService;
/**
 * 采购报表Service
 * User: cory
 * Date: 2016/11/22
 * Time: 15:24
 */
class PurcmaseReportService extends PSIBaseService {

    public function purcmaseDayByGoodsQueryData($params){
        if ($this->isNotOnline()) {
            return $this->emptyResult();
        }

        $page = $params["page"];
        $start = $params["start"];
        $limit = $params["limit"];

        $dt = $params["dt"];

        $sql = "select de.goods_id,g.code,g.name,g.spec,u.name as uname,de.goods_count,de.goods_money,de.goods_price,de.tax_rate,de.tax,de.money_with_tax
from t_po_bill as b,t_po_bill_detail as de,t_goods as g,t_goods_unit as u
where b.id=de.pobill_id and g.id=de.goods_id and g.unit_id = u.id and b.biz_dt='%s'
limit %d,%d";

        $db = M();
        $goods_data = $db->query($sql,$dt,$start,$limit);
        $goods_list = array();
        foreach ($goods_data as $goods){
            $goods_list[] = array(
              'bizDT'=>$dt,
              'goodsCode'=>$goods['code'],
              'goodsName'=>$goods['name'],
              'goodsSpec'=>$goods['spec'],
              'goodsUnit'=>$goods['uname'],
              'goods_count'=>$goods['goods_count'],
              'goods_money'=>$goods['goods_money'],
              'goods_price'=>$goods['goods_price'],
              'tax_rate'=>$goods['tax_rate'],
              'tax'=>$goods['tax'],
              'money_with_tax'=>$goods['money_with_tax'],
            );
        }

        //查询数据总条数目
        $sql = "select count(1) as cnt
from t_po_bill as b,t_po_bill_detail as de,t_goods as g
where b.id=de.pobill_id and g.id=de.goods_id and b.biz_dt='%s'";
        $data = $db->query($sql,$dt);
        $cnt = $data[0]['cnt'];

        return array(
            "dataList" => $goods_list,
            "totalCount" => $cnt
        );
    }

    /**
     * 采购日报表(按商品汇总) - 查询汇总数据
     */
    public function purcmaseDayByGoodsSummaryQueryData($params) {
        if ($this->isNotOnline()) {
            return $this->emptyResult();
        }

        return $this->purcmaseDaySummaryQueryData($params);
    }

    private function purcmaseDaySummaryQueryData($params) {
        $dt = $params["dt"];

        $result = array();
        $result[0]["bizDT"] = $dt;

        $sql = "select avg(de.tax_rate),sum(de.tax),sum(de.goods_money),sum(de.money_with_tax)
from t_po_bill as b,t_po_bill_detail as de,t_goods as g
where b.id=de.pobill_id and g.id=de.goods_id and b.biz_dt='%s'";

        $db = M();
        $sum = $db->query($sql,$dt);
        $result[0]['tax_rate'] = $sum[0]['avg(de.tax_rate)'];
        $result[0]['tax'] = $sum[0]['sum(de.tax)'];
        $result[0]['goods_money'] = $sum[0]['sum(de.goods_money)'];
        $result[0]['money_with_tax'] = $sum[0]['sum(de.money_with_tax)'];
        return $result;
    }
    /**
     * 采购月报表
     */
    public function purcmaseMonthByGoodsQueryData($params){
        if ($this->isNotOnline()) {
            return $this->emptyResult();
        }

        $page = $params["page"];
        $start = $params["start"];
        $limit = $params["limit"];

        $year = $params["year"];
        $month = $params["month"];

        $bizDT = '';
        if ($month < 10) {
            $bizDT = "$year-0$month";
        } else {
            $bizDT = "$year-$month";
        }

        $sql = "select *
from t_inventory_detail i
where year(i.biz_date) = %d and month(i.biz_date) = %d
limit %d,%d";

        $db = M();
        $goods_data = $db->query($sql,$year,$month,$start,$limit);
        $goods_list = array();
        foreach ($goods_data as $goods){
            $goods_list[] = array(
                'bizDT'=>$bizDT,
                'goodsCode'=>$goods['code'],
                'goodsName'=>$goods['name'],
                'goodsSpec'=>$goods['spec'],
                'goodsUnit'=>$goods['uname'],
                'goods_count'=>$goods['goods_count'],
                'goods_money'=>$goods['goods_money'],
                'goods_price'=>$goods['goods_price'],
                'tax_rate'=>$goods['tax_rate'],
                'tax'=>$goods['tax'],
                'money_with_tax'=>$goods['money_with_tax'],
            );
        }

        //查询数据总条数目
        $sql = "select count(1) as cnt
from t_po_bill as b,t_po_bill_detail as de,t_goods as g
where b.id=de.pobill_id and g.id=de.goods_id and year(b.biz_dt) = %d and month(b.biz_dt) = %d";
        $data = $db->query($sql,$year,$month);
        $cnt = $data[0]['cnt'];

        return array(
            "dataList" => $goods_list,
            "totalCount" => $cnt
        );
    }

    public function purcmaseMonthByGoodsSummaryQueryData($params){
        if ($this->isNotOnline()) {
            return $this->emptyResult();
        }

        return $this->purcmaseMonthSummaryQueryData($params);
    }

    private function purcmaseMonthSummaryQueryData($params) {

        $year = $params["year"];
        $month = $params["month"];

        $result = array();
        if ($month < 10) {
            $result[0]["bizDT"] = "$year-0$month";
        } else {
            $result[0]["bizDT"] = "$year-$month";
        }

        $sql = "select avg(de.tax_rate),sum(de.tax),sum(de.goods_money),sum(de.money_with_tax)
from t_po_bill as b,t_po_bill_detail as de,t_goods as g
where b.id=de.pobill_id and g.id=de.goods_id and year(b.biz_dt) = %d and month(b.biz_dt) = %d";

        $db = M();
        $sum = $db->query($sql,$year,$month);
        $result[0]['tax_rate'] = $sum[0]['avg(de.tax_rate)'];
        $result[0]['tax'] = $sum[0]['sum(de.tax)'];
        $result[0]['goods_money'] = $sum[0]['sum(de.goods_money)'];
        $result[0]['money_with_tax'] = $sum[0]['sum(de.money_with_tax)'];
        return $result;
    }

    /**
     * 采购日报表(按供应商汇总) - 查询数据
     */
    public function purcmaseDayByCustomerQueryData($params) {
        if ($this->isNotOnline()) {
            return $this->emptyResult();
        }

        $page = $params["page"];
        $start = $params["start"];
        $limit = $params["limit"];

        $dt = $params["dt"];

        $result = array();

        $us = new UserService();
        $companyId = $us->getCompanyId();
        $db = M();
        $sql = "select c.id, c.code, c.name
				from t_supplier c
				where c.id in(
					select distinct w.supplier_id
					from t_po_bill w
					where w.biz_dt = '%s' and w.company_id = '%s'
					)
				order by c.code
				limit %d, %d";
        $items = $db->query($sql, $dt, $companyId, $start, $limit);

        $result = array();
        foreach ( $items as $i => $v ) {
            $sql = "select avg(de.tax_rate),sum(de.tax),sum(de.goods_money),sum(de.money_with_tax)
from t_po_bill as b,t_po_bill_detail as de,t_goods as g
where b.id=de.pobill_id and g.id=de.goods_id and b.biz_dt='%s' and b.supplier_id='%s'";

            $db = M();
            $sum = $db->query($sql,$dt,$v['id']);
            $re = array();
            $re['tax_rate'] = $sum[0]['avg(de.tax_rate)'];//平均税率
            $re['tax'] = $sum[0]['sum(de.tax)'];//税金
            $re['goods_money'] = $sum[0]['sum(de.goods_money)'];//采购金额
            $re['money_with_tax'] = $sum[0]['sum(de.money_with_tax)'];//价税合计
            $re['customerCode'] = $v['code'];//价客户编号
            $re['customerName'] = $v['name'];//价客户名称
            $re['bizDT'] = $dt;//价客户名称
            $result[] = $re;
        }

        $sql = "select count(*) as cnt
				from t_supplier c
				where c.id in(
					select distinct w.supplier_id
					from t_po_bill w
					where w.biz_dt = '%s' and w.company_id = '%s'
					)";
        $data = $db->query($sql, $dt, $companyId, $dt, $companyId);
        $cnt = $data[0]["cnt"];

        return array(
            "dataList" => $result,
            "totalCount" => $cnt
        );
    }

    /**
     * 采购日报表(按客户汇总) - 查询汇总数据
     */
    public function purcmaseDayByCustomerSummaryQueryData($params) {
        if ($this->isNotOnline()) {
            return $this->emptyResult();
        }

        return $this->purcmaseDaySummaryQueryData($params);
    }

    /**
     * 采购月报表(按供应商汇总) - 查询数据
     */
    public function purcmaseMonthByCustomerQueryData($params) {
        if ($this->isNotOnline()) {
            return $this->emptyResult();
        }

        $page = $params["page"];
        $start = $params["start"];
        $limit = $params["limit"];

        $year = $params["year"];
        $month = $params["month"];

        $bizDT = '';
        if ($month < 10) {
            $bizDT = "$year-0$month";
        } else {
            $bizDT = "$year-$month";
        }


        $result = array();

        $us = new UserService();
        $companyId = $us->getCompanyId();
        $db = M();
        $sql = "select c.id, c.code, c.name
				from t_supplier c
				where c.id in(
					select distinct w.supplier_id
					from t_po_bill w
					where year(w.biz_dt) = '%s' and month(w.biz_dt) = '%s'and w.company_id = '%s'
					)
				order by c.code
				limit %d, %d";
        $items = $db->query($sql, $year,$month, $companyId, $start, $limit);


        $result = array();
        foreach ( $items as $i => $v ) {
            $sql = "select avg(de.tax_rate),sum(de.tax),sum(de.goods_money),sum(de.money_with_tax)
from t_po_bill as b,t_po_bill_detail as de,t_goods as g
where b.id=de.pobill_id and g.id=de.goods_id and year(b.biz_dt) = '%s' and month(b.biz_dt) = '%s' and b.supplier_id='%s'";

            $db = M();
            $sum = $db->query($sql,$year,$month,$v['id']);
            $re = array();
            $re['tax_rate'] = $sum[0]['avg(de.tax_rate)'];//平均税率
            $re['tax'] = $sum[0]['sum(de.tax)'];//税金
            $re['goods_money'] = $sum[0]['sum(de.goods_money)'];//采购金额
            $re['money_with_tax'] = $sum[0]['sum(de.money_with_tax)'];//价税合计
            $re['customerCode'] = $v['code'];//价客户编号
            $re['customerName'] = $v['name'];//价客户名称
            $re['bizDT'] = $bizDT;//价客户名称
            $result[] = $re;
        }

        $sql = "select count(*) as cnt
				from t_supplier c
				where c.id in(
					select distinct w.supplier_id
					from t_po_bill w
					where year(w.biz_dt) = '%s' and month(w.biz_dt) = '%s' and w.company_id = '%s'
					)";
        $data = $db->query($sql, $bizDT, $companyId, $year,$month, $companyId);
        $cnt = $data[0]["cnt"];

        return array(
            "dataList" => $result,
            "totalCount" => $cnt
        );
    }
    /**
     * 采购月报表(按供应商汇总) - 查询汇总数据
     */
    public function purcmaseMonthByCustomerSummaryQueryData($params) {
        if ($this->isNotOnline()) {
            return $this->emptyResult();
        }

        return $this->purcmaseMonthSummaryQueryData($params);
    }

}